package dao.impl;

import dao.StaffsDao;
import entity.Staff;
import util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StaffDaoImpl4Jdbc implements StaffsDao{


    @Override
    public int insert(Staff staffs) {
        int row = 0 ;

        // 第一：定义要操作数据库的 SQL 语句
        String sql = "insert into staffs(name,age,phone,sta_pos) values (?,?,?,?)" ;

        // 第二：获取连接对象
        Connection conn = DbUtil.getConnection();


        PreparedStatement ps = null ;

        try {
            // 第三：预编译SQL语句
            ps = conn.prepareStatement(sql);

            // 第四：填充参数
            ps.setString(1,staffs.getName());
            ps.setInt(2,staffs.getAge());
            ps.setString(3,staffs.getPhone());
            ps.setString(4,staffs.getStaPos());

            // 第五：执行SQL语句
            row = ps.executeUpdate();

            // 第六：对执行结果进行处理
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 第七：关闭对象
            DbUtil.close(null,ps,conn);
        }

        return row;
    }

    @Override
    public int delete(String id) {
        int r  = 0;
        String sql = "delete from staffs where id=? ";
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            // 第三：预编译SQL语句
            pstmt = conn.prepareStatement(sql);

            // 第四：填充占位符中的数据
            pstmt.setInt(1, Integer.parseInt(id));

            // 第五：执行SQL语句
            r = pstmt.executeUpdate();

            // 第六：对执行的结果进行处理
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return r;
    }

    @Override
    public int update(Staff s) {
        int r = 0;
        String sql = "update staffs set name=?,age=?,phone=?,sta_pos=? where id=?";
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;

        try {
            // 第三：预编译SQL语句
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,s.getName());
            pstmt.setInt(2,s.getAge());
            pstmt.setString(3,s.getPhone());
            pstmt.setString(4,s.getStaPos());
            pstmt.setInt(3,s.getId());

            r = pstmt.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return r;
    }

    @Override
    public List<Staff> selectAll() {
        List<Staff> list = new ArrayList<>();
        String sql = "select id,name,age,phone,sta_pos,add_time,update_time from staffs ";
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rst = null;
        try {
            // 第三：预编译SQL语句
            pstmt = conn.prepareStatement(sql);
            rst = pstmt.executeQuery();
            while (rst.next()) {
                Staff staff = new Staff();
                staff.setId(rst.getInt(1));
                staff.setName(rst.getString(2));
                staff.setAge(rst.getInt(3));
                staff.setPhone(rst.getString(4));
                staff.setStaPos(rst.getString(5));

                // 处理 Timestamp 转换为 LocalDateTime
                staff.setAddTime(rst.getTimestamp(6).toLocalDateTime());
                staff.setUpdateTime(rst.getTimestamp(7).toLocalDateTime());

                list.add(staff);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return  list;
    }

    @Override
    public List<Staff> selectByPage(int currentPage, int pageSize) {
        List<Staff> list = new ArrayList<>();

        // 第一：定义要操作数据库的 SQL 语句
        String sql = "select * from staffs limit ?,?" ;

        // 第二：获取连接对象
        Connection conn = DbUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, pageSize); // pageSize
            ps.setInt(2, (currentPage - 1) * pageSize); // offset

            rs = ps.executeQuery();

            while (rs.next()) {
                Staff staff = new Staff();
                staff.setId(rs.getInt(1));
                staff.setName(rs.getString(2));
                staff.setAge(rs.getInt(3));
                staff.setPhone(rs.getString(4));
                staff.setStaPos(rs.getString(5));
                staff.setAddTime(rs.getTimestamp(6).toLocalDateTime());
                staff.setUpdateTime(rs.getTimestamp(7).toLocalDateTime());
                list.add(staff);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(rs, ps, conn);
        }
        return list;
    }

    @Override
    public Staff selectById(int id) {
        String sql = "select id,name,age,phone,sta_pos as staPos,add_time as addTime,update_time as updateTime from staffs where id=?" ;
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rst = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rst = pstmt.executeQuery();
            if (rst.next()) {
                Staff staff = new Staff();
                staff.setId(rst.getInt(1));
                staff.setName(rst.getString(2));
                staff.setAge(rst.getInt(3));
                staff.setPhone(rst.getString(4));
                staff.setStaPos(rst.getString(5));
                staff.setAddTime(rst.getTimestamp(6).toLocalDateTime());
                staff.setUpdateTime(rst.getTimestamp(7).toLocalDateTime());
                return staff;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(rst, pstmt, conn);
        }
        return null;
    }
}
